﻿#pragma once

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/odbc/odbc.h>
#include <sqlpp11/sqlite3/sqlite3.h>
#include "sqlpp11orm.h"

#include "OCMain/Environment.h"
#include "OCMain/Log.h"
#include "OCMain/Timer.h"
#include "OCMain/FileUtil.h"
#include "OCMain/Date.h"
#include "OCServer/ServerUtil.h"

using namespace OCPP;

inline String formatTime(const sqlpp::chrono::microsecond_point& value, bool showMicroseconds = true)
{
	Date date(value.time_since_epoch().count());
	return date.toFormattedString(showMicroseconds);
}

inline String formatTime(const std::chrono::microseconds& value)
{
	Date date(value.count());
	return date.toFormattedString(false);
}

void OCTest::test1()
{
	using Data = struct
	{
		std::vector<String> time;
		std::vector<float> prec;
		std::vector<float> data;
	};

	using DataMap = std::map<String, Data>;

	DataMap dmap;

	String fileName = "I:\\Project\\官山河流域\\2023\\吕家河.csv";
	fileName = StringUtil::UTF8ToANSI(fileName);
	String filePath = FileUtil::getFilePath(fileName);

	std::ifstream ifs(fileName);
	String header;
	getline(ifs, header);
	header = StringUtil::UTF8ToANSI(header);
	StringVector sv0 = StringUtil::split(header, ",");
	String line;
	while (getline(ifs, line))
	{
		line = StringUtil::UTF8ToANSI(line);
		StringVector sv = StringUtil::split(line, ",");
		String time = sv[1];
		String type = sv[2];
		if (type == "case") continue;
		dmap[type].time.push_back(time);
		dmap[type].prec.push_back((float)std::atof(sv[3].c_str()));
		dmap[type].data.push_back((float)std::atof(sv[5].c_str()));
	}

	Json::Value root;

	for (auto [type, data] : dmap)
	{
		Json::Value valueT(Json::arrayValue);
		Json::Value valueP(Json::arrayValue);
		Json::Value valueV(Json::arrayValue);

		OCPP_LOG_INFO << type << " maxValue:";
		float maxValue = 0.0;
		for (auto i = 0; i < data.time.size(); i++)
		{
			auto& time = data.time[i];
			auto& d = data.data[i];
			auto& prec = data.prec[i];
			valueT.append(time);
			valueV.append(d);
			valueP.append(prec);
			if (d > maxValue) maxValue = d;
		}

		OCPP_LOG_INFO << maxValue << " \n";

		root[type]["time"] = valueT;
		root[type]["prcp"] = valueP;
		root[type]["data"] = valueV;
	}

	StringStream sstream;
	Json::StreamWriterBuilder jswBuilder;
	jswBuilder["emitUTF8"] = true;
	std::unique_ptr<Json::StreamWriter> jsWriter(jswBuilder.newStreamWriter());
	jsWriter->write(root, &sstream);
	//OCPP_LOG_INFO << sstream.str() << "\n";

	std::ofstream ofs(filePath + "\\5-500.json");
	ofs << StringUtil::ANSIToUTF8(sstream.str());
}

void OCTest::test2()
{
	//String driver = "ODBC Driver 18 for SQL Server";
	//String database = "flood";
	//String server = "10.104.207.96,1433";
	//String user = "sa";
	//String pswd = "hxlc";

	String driver = "ODBC Driver 18 for SQL Server";
	String database = "WH_TJ";
	String server = "122.112.252.181,1433";
	String user = "sa";
	String pswd = "HBTJ@20220209";

	namespace odbc = sqlpp::odbc;
	odbc::driver_connection_config config;
	config.connection = str_format("DRIVER=%s;SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;TrustServerCertificate=yes;"
		, driver.c_str()
		, server.c_str()
		, database.c_str()
		, user.c_str()
		, pswd.c_str()
	);
	config.type = odbc::ODBC_Type::TSQL;
	config.debug = false;
	std::unique_ptr<odbc::connection> db;

	try {
		db.reset(new odbc::connection(config));
		db->execute("USE " + database);

		Json::Value data;

		{
			Tab_xb_waterdata::xb_waterdata xb_waterdata = {};
			Tab_xb_device::xb_device xb_device = {};

			auto result = (*db)(select(
				all_of(xb_waterdata),
				xb_device.devicename
			)
				.from(xb_waterdata.join(xb_device).on(xb_waterdata.deviceid == xb_device.deviceid))
				.where(xb_waterdata.id < 100));
			for (const auto& row : result)
			{
				Json::Value val;
				val[Tab_xb_waterdata::Field::id] = (Json::Int64)row.id.value();
				val[Tab_xb_waterdata::Field::deviceid] = (Json::Int)row.deviceid.value();
				val[Tab_xb_waterdata::Field::water] = row.water.value();
				val[Tab_xb_waterdata::Field::createdate] = formatTime(row.createdate.value());
				val[Tab_xb_device::Field::devicename] = row.devicename.value();
				data.append(val);
			};
		}

		OCPP_LOG_INFO << "\n" << ServerUtil::jsonToString(data, " ") << "\n";
		OCPP_LOG_INFO << "结果条数:" << data.size() << "\n";
	}
	catch (const std::exception& e) {
		OCPP_LOG_ERROR << "Encountered error: " << e.what() << '\n';
	}
	catch (...) {
		OCPP_LOG_ERROR << "unknown error.\n";
	}
}

void OCTest::test3()
{
	namespace sql = sqlpp::sqlite3;
	sql::connection_config config;
	config.path_to_database = "F:\\opt\\data\\zjdata3d\\ZJNow3D\\WeatherTimer2.db";
	config.flags = SQLITE_OPEN_READWRITE;
	config.debug = false;

	try {
		sql::connection db(config);

		if (!db.is_connected())
		{
			OCPP_LOG_ERROR << "no connect.";
		}

		Json::Value data;

		//{
		//	AUTO_TIMER_ELAPSE_MESSAGE("插入耗时");
		//	Tab_Weather::anal1km table = {};
		//	db.start_transaction();
		//	for (auto i = 0; i < 500000; i++)
		//	{
		//		db(insert_into(table).set(table.timestamp = i));
		//	}
		//	db.commit_transaction();
		//	return;
		//}

		{
			Tab_Weather::fcst3km table = {};
			auto result = (db)(select(all_of(table))
				.from(table).unconditionally());
			for (const auto& row : result)
			{
				Json::Value val;
				val[Tab_Weather::Field::g] = (Json::Int64)row.g.value();
				val[Tab_Weather::Field::timestamp] = (Json::Int64)row.timestamp.value();
				data.append(val);
			};
		}

		OCPP_LOG_INFO << "\n" << ServerUtil::jsonToString(data, " ") << "\n";
		OCPP_LOG_INFO << "结果条数:" << data.size() << "\n";
	}
	catch (const std::exception& e) {
		OCPP_LOG_ERROR << "Encountered error: " << e.what() << '\n';
	}
	catch (...) {
		OCPP_LOG_ERROR << "unknown error.\n";
	}
}